USE [AdventureWorks2012]
GO
-- PREGUNTA 1
DECLARE @Id INT
		, @Nombre NVARCHAR(MAX)
		, @Precio DECIMAL
		, @NuevoPrecio DECIMAL
		, @Aumento DECIMAL
DECLARE @Temp TABLE (Nombre VARCHAR(MAX), PrecioAnt DECIMAL, Aumento DECIMAL, PrecioActual DECIMAL)
DECLARE qModPrice CURSOR FOR
	SELECT ProductID, Name, ListPrice
		FROM Production.Product
OPEN qModPrice
	FETCH NEXT FROM qModPrice INTO @Id, @Nombre, @Precio
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @Aumento = 
			CASE 
				WHEN @Precio < 100 THEN (@Precio*0.15)
				WHEN @Precio >= 100 THEN (@Precio*.10)
			END
		SET @NuevoPrecio = @Precio + @Aumento
		UPDATE Production.Product 
			SET ListPrice = @NuevoPrecio
			WHERE ProductID = @Id 
				AND Name = @Nombre
		BEGIN TRY
			INSERT INTO Production.ProductListPriceHistory(ProductID, StartDate, ListPrice, ModifiedDate)
				VALUES(@Id, CURRENT_TIMESTAMP, @NuevoPrecio, CURRENT_TIMESTAMP)
		END TRY
		BEGIN CATCH
			PRINT 'No se puede insertar valores iguales a 0 en ListPrice de Production.ProductListPriceHistory'
		END CATCH
		INSERT INTO @Temp
			VALUES(@Nombre, @Precio, @Aumento, @NuevoPrecio)
		FETCH NEXT FROM qModPrice INTO @Id, @Nombre, @Precio
	END
CLOSE qModPrice
DEALLOCATE qModPrice
SELECT * FROM @Temp
GO

-- PREGUNTA 2
USE [AdventureWorks2012]
IF NOT EXISTS( SELECT * FROM sys.tables WHERE Name = 'Log' )
BEGIN 
	CREATE TABLE [Log](
		Id INT IDENTITY (1, 1)
		, DatosAnteriores VARCHAR(MAX)
		, NuevosDatos VARCHAR(MAX)
		, Operacion VARCHAR(MAX)
		, Fecha DATETIME DEFAULT CURRENT_TIMESTAMP
	)
END
GO
DECLARE @PrePrevious INT = 0
		, @Previous INT = 1
		, @Current INT = 0
DECLARE @TempId TABLE (Id INT)
INSERT INTO @TempId
	VALUES(@PrePrevious)
WHILE (@Current < 30000)
BEGIN
	SET @Current = @PrePrevious + @Previous
	SET @PrePrevious = @Previous
	SET @Previous = @Current
	INSERT INTO @TempId
		VALUES(@PrePrevious)
END
--SELECT * FROM @TempId
SET IDENTITY_INSERT NORTHWND.dbo.Employees ON
MERGE INTO NORTHWND.dbo.Employees AS tgt
	USING(
		SELECT * from Person.Person WHERE BusinessEntityID IN (SELECT * FROM @TempId)
	)AS src ON src.BusinessEntityID = tgt.EmployeeID
	WHEN MATCHED THEN UPDATE SET
		tgt.FirstName = src.FirstName + ISNULL(src.MiddleName,'')
		, tgt.LastName = src.LastName
		, tgt.TitleOfCourtesy = src.Title
	WHEN NOT MATCHED THEN INSERT(EmployeeID, FirstName, LastName, TitleOfCourtesy)
		VALUES(src.BusinessEntityID, src.FirstName + ISNULL(src.MiddleName,''), src.LastName, src.Title)
	OUTPUT 'FirstName: ' + ISNULL(deleted.FirstName, '') + ' LastName: ' + ISNULL(deleted.LastName, '') + ' TitleOfCourtesy ' + ISNULL(deleted.TitleOfCourtesy, '')
		   , 'FirstName: ' + ISNULL(inserted.FirstName, '') + ' LastName: ' + ISNULL(inserted.LastName, '') + ' TitleOfCourtesy: ' + ISNULL(inserted.TitleOfCourtesy, '')
		   , $ACTION
		   , CURRENT_TIMESTAMP
		INTO [Log] (DatosAnteriores, NuevosDatos, Operacion,Fecha);
SELECT * FROM NORTHWND.dbo.Employees
SELECT * FROM [Log]
SET IDENTITY_INSERT NORTHWND.dbo.Employees ON
GO